/*
 * MIT License
 *
 * Copyright (c) 2023-present, tangli
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

package tony.test.mybatis.datascope.mysql

import org.junit.jupiter.api.Assertions.assertFalse
import org.junit.jupiter.api.Assertions.assertTrue
import org.junit.jupiter.api.BeforeEach
import org.junit.jupiter.api.DisplayName
import org.junit.jupiter.api.Nested
import org.junit.jupiter.api.Test
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.test.context.ActiveProfiles
import tony.mybatis.datascope.IDataScopeContext
import tony.mybatis.datascope.handler.DefaultScopedDataPermissionHandler
import tony.mybatis.datascope.interceptor.ScopedDataPermissionInterceptor
import tony.mybatis.datascope.model.ScopedColumn
import tony.test.mybatis.datascope.DataScopeBaseTest

/**
 * MySQL特有语法正确性验证测试
 * @author tangli
 * @date 2025/09/21 10:00
 */
@ActiveProfiles("mysql")
@DisplayName("MySQL特有语法正确性验证测试")
class DataScopeMySqlTest : DataScopeBaseTest() {

    @Autowired
    private lateinit var dataScopeContext: IDataScopeContext

    private lateinit var interceptor: ScopedDataPermissionInterceptor

    @BeforeEach
    fun setUp() {
        interceptor = ScopedDataPermissionInterceptor(DefaultScopedDataPermissionHandler())
    }

    @Nested
    @DisplayName("MySQL标识符语法验证")
    inner class MySqlIdentifierTest {

        @Test
        @DisplayName("反引号表名:正确处理反引号")
        fun testBackquoteTableName() {
            val originalSql = "SELECT * FROM `sys_dict` d"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.backquote.table")
                }

            logger.info("=== MySQL反引号表名测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("`sys_dict`"))
            logger.info("反引号表名处理验证完成")
        }

        @Test
        @DisplayName("反引号列名:正确处理反引号列名")
        fun testBackquoteColumnName() {
            val customColumn = ScopedColumn("d", "`my_user_id`", "`my_org_id`")

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .scopedColumns(listOf(customColumn))
                .call {
                    interceptor.parserSingle("SELECT * FROM sys_dict d", "test.mysql.backquote.column")
                }

            logger.info("=== MySQL反引号列名测试 ===")
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.`my_user_id` = 'user1'"))
            assertFalse(result.contains("creator_id"))
            logger.info("反引号列名处理验证完成")
        }

        @Test
        @DisplayName("数据库名.表名:正确处理完整表名")
        fun testDatabaseTableName() {
            val originalSql = "SELECT * FROM `tony_api`.`sys_dict` d"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.database.table")
                }

            logger.info("=== MySQL完整表名测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("`tony_api`.`sys_dict`"))
            logger.info("完整表名处理验证完成")
        }

        @Test
        @DisplayName("混合标识符:反引号和普通标识符混合")
        fun testMixedIdentifiers() {
            val originalSql = "SELECT d.`dict_name`, d.dict_code FROM `sys_dict` d WHERE d.`buildIn` = 1"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.mixed.identifiers")
                }

            logger.info("=== MySQL混合标识符测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("d.`dict_name`"))
            assertTrue(result.contains("d.dict_code"))
            assertTrue(result.contains("d.`buildIn` = 1"))
            logger.info("混合标识符处理验证完成")
        }
    }

    @Nested
    @DisplayName("MySQL特有函数和语法验证")
    inner class MySqlFunctionTest {

        @Test
        @DisplayName("LIMIT语法:分页权限正确注入")
        fun testMySqlLimitSyntax() {
            val originalSql = "SELECT * FROM sys_dict d LIMIT 10 OFFSET 20"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.limit")
                }

            logger.info("=== MySQL LIMIT语法测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("LIMIT 10 OFFSET 20"))
            logger.info("LIMIT语法处理验证完成")
        }

        @Test
        @DisplayName("JSON函数:复杂查询权限注入")
        fun testMySqlJsonFunction() {
            val originalSql = """
                SELECT * FROM sys_dict d
                WHERE JSON_EXTRACT(d.dict_meta, '$.type') = 'system'
                  AND JSON_UNQUOTE(JSON_EXTRACT(d.dict_meta, '$.category')) IN ('config', 'enum')
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.json.function")
                }

            logger.info("=== MySQL JSON函数测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("JSON_EXTRACT"))
            assertTrue(result.contains("'$.type'"))
            assertTrue(result.contains("JSON_UNQUOTE"))
            assertTrue(result.contains("'$.category'"))
            logger.info("JSON函数处理验证完成")
        }

        @Test
        @DisplayName("MySQL窗口函数:复杂查询权限注入")
        fun testMySqlWindowFunction() {
            val originalSql = """
                SELECT d.*,
                       ROW_NUMBER() OVER (PARTITION BY d.dict_type_id ORDER BY d.create_time) as rn,
                       RANK() OVER (ORDER BY d.sort DESC) as sort_rank,
                       LAG(d.dict_name, 1) OVER (PARTITION BY d.dict_type_id ORDER BY d.create_time) as prev_name
                FROM sys_dict d
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.window.function")
                }

            logger.info("=== MySQL窗口函数测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("ROW_NUMBER()"))
            assertTrue(result.contains("PARTITION BY"))
            assertTrue(result.contains("RANK()"))
            assertTrue(result.contains("LAG("))
            logger.info("窗口函数处理验证完成")
        }

        @Test
        @DisplayName("MySQL日期函数:日期时间函数权限注入")
        fun testMySqlDateFunctions() {
            val originalSql = """
                SELECT d.*
                FROM sys_dict d
                WHERE d.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                  AND YEAR(d.create_time) = 2025
                  AND MONTH(d.create_time) >= 9
                  AND DAYOFWEEK(d.create_time) NOT IN (1, 7)
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.date.functions")
                }

            logger.info("=== MySQL日期函数测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("DATE_SUB"))
            assertTrue(result.contains("NOW()"))
            assertTrue(result.contains("YEAR("))
            assertTrue(result.contains("MONTH("))
            assertTrue(result.contains("DAYOFWEEK("))
            logger.info("日期函数处理验证完成")
        }

        @Test
        @DisplayName("MySQL聚合函数:GROUP BY场景权限注入")
        fun testMySqlAggregateFunction() {
            val originalSql = """
                SELECT d.dict_type_id,
                       COUNT(*) as dict_count,
                       GROUP_CONCAT(d.dict_name SEPARATOR ', ') as dict_names,
                       MAX(d.create_time) as latest_time,
                       AVG(d.sort) as avg_sort
                FROM sys_dict d
                WHERE d.build_in = 1
                GROUP BY d.dict_type_id
                HAVING COUNT(*) > 2
                ORDER BY dict_count DESC
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.aggregate.function")
                }

            logger.info("=== MySQL聚合函数测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("COUNT(*)"))
            assertTrue(result.contains("GROUP_CONCAT"))
            assertTrue(result.contains("SEPARATOR"))
            assertTrue(result.contains("GROUP BY"))
            assertTrue(result.contains("HAVING"))
            logger.info("聚合函数处理验证完成")
        }

        @Test
        @DisplayName("ON DUPLICATE KEY UPDATE:MySQL特有语法")
        fun testOnDuplicateKeyUpdate() {
            val originalSql = """
                INSERT INTO sys_dict (dict_id, dict_name, creator_id)
                VALUES ('1', 'test', 'user1')
                ON DUPLICATE KEY UPDATE
                    dict_name = VALUES(dict_name),
                    update_time = NOW(),
                    updator_id = VALUES(creator_id)
            """.trimIndent()

            // INSERT语句不应该被数据权限拦截，但我们测试解析不报错
            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    try {
                        interceptor.parserSingle(originalSql, "test.mysql.duplicate.key")
                    } catch (e: Exception) {
                        logger.info("INSERT语句不被数据权限处理，返回原SQL: {}", e.message)
                        originalSql // 如果不支持INSERT则返回原SQL
                    }
                }

            logger.info("=== MySQL ON DUPLICATE KEY测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("ON DUPLICATE KEY UPDATE"))
            assertTrue(result.contains("VALUES("))
            logger.info("ON DUPLICATE KEY处理验证完成")
        }
    }

    @Nested
    @DisplayName("MySQL字符集和特殊字符验证")
    inner class MySqlCharsetTest {

        @Test
        @DisplayName("中文字符:正确处理UTF8字符")
        fun testChineseCharacters() {
            val result = dataScopeContext.dataScope()
                .userIdList(listOf("用户1", "用户2", "管理员"))
                .call {
                    interceptor.parserSingle("SELECT * FROM sys_dict d", "test.mysql.chinese")
                }

            logger.info("=== MySQL中文字符测试 ===")
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id IN ('用户1', '用户2', '管理员')"))
            logger.info("中文字符处理验证完成")
        }

        @Test
        @DisplayName("特殊字符转义:正确处理引号等特殊字符")
        fun testSpecialCharacterEscape() {
            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user'1", "user\"2", "user\\3", "user`4"))
                .call {
                    interceptor.parserSingle("SELECT * FROM sys_dict d", "test.mysql.escape")
                }

            logger.info("=== MySQL特殊字符转义测试 ===")
            logger.info("最终SQL: {}", result)

            // 验证特殊字符被正确处理（具体转义规则依赖于JSqlParser）
            assertTrue(result.contains("d.creator_id IN"))
            assertTrue(result.contains("user"))
            logger.info("特殊字符转义处理验证完成")
        }

        @Test
        @DisplayName("MySQL关键字:正确处理MySQL保留关键字")
        fun testMySqlKeywords() {
            val originalSql = """
                SELECT d.`order`, d.`desc`, d.`limit`
                FROM sys_dict d
                WHERE d.`index` > 0
                  AND d.`key` IS NOT NULL
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.keywords")
                }

            logger.info("=== MySQL关键字测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("d.`order`"))
            assertTrue(result.contains("d.`desc`"))
            assertTrue(result.contains("d.`limit`"))
            assertTrue(result.contains("d.`index`"))
            assertTrue(result.contains("d.`key`"))
            logger.info("MySQL关键字处理验证完成")
        }

        @Test
        @DisplayName("Emoji字符:正确处理Emoji和特殊Unicode字符")
        fun testEmojiCharacters() {
            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user😀", "admin🚀", "test💯"))
                .call {
                    interceptor.parserSingle("SELECT * FROM sys_dict d", "test.mysql.emoji")
                }

            logger.info("=== MySQL Emoji字符测试 ===")
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id IN"))
            assertTrue(result.contains("user😀") || result.contains("user"))
            logger.info("Emoji字符处理验证完成")
        }
    }

    @Nested
    @DisplayName("MySQL存储引擎特性验证")
    inner class MySqlEngineTest {

        @Test
        @DisplayName("MySQL事务:权限在事务中正确工作")
        fun testMySqlTransaction() {
            val originalSql = "SELECT * FROM sys_dict d WHERE d.build_in = 1 FOR UPDATE"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.transaction")
                }

            logger.info("=== MySQL事务测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("FOR UPDATE"))
            logger.info("MySQL事务处理验证完成")
        }

        @Test
        @DisplayName("MySQL UNION查询:UNION语法权限注入")
        fun testMySqlUnionQuery() {
            val originalSql = """
                SELECT dict_id, dict_name, 'dict' as source_type FROM sys_dict WHERE build_in = 1
                UNION ALL
                SELECT dict_type_id, dict_type_name, 'type' as source_type FROM sys_dict_type WHERE build_in = 1
                ORDER BY dict_name
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.union")
                }

            logger.info("=== MySQL UNION查询测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            // UNION查询可能在多个地方注入权限条件
            assertTrue(result.contains("creator_id = 'user1'", true))
            assertTrue(result.contains("UNION ALL", true))
            assertTrue(result.contains("'dict' as source_type", true))
            assertTrue(result.contains("'type' as source_type", true))
            logger.info("MySQL UNION查询处理验证完成")
        }
    }

    @Nested
    @DisplayName("MySQL复杂场景验证")
    inner class MySqlComplexScenarioTest {

        @Test
        @DisplayName("MySQL全文搜索:MATCH AGAINST语法")
        fun testMySqlFullTextSearch() {
            val originalSql = """
                SELECT d.*, MATCH(d.dict_name, d.remark) AGAINST ('search term' IN NATURAL LANGUAGE MODE) as relevance
                FROM sys_dict d
                WHERE MATCH(d.dict_name, d.remark) AGAINST ('search term' IN NATURAL LANGUAGE MODE)
                ORDER BY relevance DESC
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.fulltext")
                }

            logger.info("=== MySQL全文搜索测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'"))
            assertTrue(result.contains("MATCH ("))
            assertTrue(result.contains("AGAINST"))
            assertTrue(result.contains("IN NATURAL LANGUAGE MODE"))
            logger.info("MySQL全文搜索处理验证完成")
        }

        @Test
        @DisplayName("MySQL存储过程调用:CALL语法")
        fun testMySqlStoredProcedureCall() {
            val originalSql = "CALL get_dict_by_type('system', @result_count)"

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .call {
                    try {
                        interceptor.parserSingle(originalSql, "test.mysql.procedure")
                    } catch (e: Exception) {
                        logger.info("存储过程调用不被数据权限处理: {}", e.message)
                        originalSql
                    }
                }

            logger.info("=== MySQL存储过程调用测试 ===")
            logger.info("原始SQL: {}", originalSql)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("CALL"))
            assertTrue(result.contains("get_dict_by_type"))
            logger.info("MySQL存储过程调用处理验证完成")
        }

        @Test
        @DisplayName("MySQL复合查询:多种语法组合")
        fun testMySqlComplexCombination() {
            val originalSql = """
                SELECT
                    d.dict_id,
                    d.dict_name,
                    JSON_EXTRACT(d.dict_meta, '$.priority') as priority,
                    CASE
                        WHEN d.sort < 10 THEN 'high'
                        WHEN d.sort < 100 THEN 'medium'
                        ELSE 'low'
                    END as priority_level,
                    (SELECT COUNT(*) FROM sys_dict_type t WHERE t.dict_type_id = d.dict_type_id) as type_dict_count
                FROM sys_dict d
                WHERE d.build_in = 1
                  AND d.create_time >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
                  AND JSON_EXTRACT(d.dict_meta, '$.visible') = true
                ORDER BY
                    FIELD(d.dict_type_id, 'system', 'business', 'config'),
                    d.sort ASC,
                    d.create_time DESC
                LIMIT 50
            """.trimIndent()

            val result = dataScopeContext.dataScope()
                .userIdList(listOf("user1"))
                .orgIdList(listOf("org1"))
                .call {
                    interceptor.parserSingle(originalSql, "test.mysql.complex.combination")
                }

            logger.info("=== MySQL复合查询测试 ===")
            logger.info("原始SQL长度: {}", originalSql.length)
            logger.info("最终SQL: {}", result)

            assertTrue(result.contains("d.creator_id = 'user1'") || result.contains("d.creator_org_id = 'org1'"))
            assertTrue(result.contains("JSON_EXTRACT"))
            assertTrue(result.contains("CASE"))
            assertTrue(result.contains("DATE_SUB"))
            assertTrue(result.contains("FIELD("))
            assertTrue(result.contains("LIMIT 50"))
            logger.info("MySQL复合查询处理验证完成")
        }
    }
}
